package jdbc;

import org.junit.Test;

import java.sql.*;

public class StudentDAO {
    private static final String URL = "jdbc:mysql://10.10.61.14:8080/jdbc";
    private static final String USER = "jdbc";
    private static final String PASSWORD = "jdbc123!";

	@Test
	public void testFindByNo() {
		// String no = "20118765432";
		String no = "12' or '1'='1";
		Student s = findByNo2(no);
		System.out.println(s);
	}

	public Student findByNo(String no) {
		// TODO 不要这样拼接sql
		String sql = "select * from Student where no='" + no + "'";
		System.out.println(sql);
		try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement stmt = connection.createStatement();
			 ResultSet rs = stmt.executeQuery(sql);) {
			if (!rs.next()) {
				return null;
			}
			return createStudent(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	// 从rs 中读取数据，建立 Student 对象
	Student createStudent(ResultSet rs) throws SQLException {
		Student s = new Student();
		s.setId(rs.getInt("Id"));
		s.setNo(rs.getString("No"));
		s.setName(rs.getString("Name"));
		s.setAge(rs.getInt("Age"));
		s.setBirthday(rs.getDate("Birthday"));
		if (rs.wasNull())
			s.setBirthday(new Date(System.currentTimeMillis()));
		s.setPhoto(rs.getBlob("Photo"));
		return s;
	}

	public Student findByNo2(String no) {
		String sql = "select * from t_insert where no=?";
		try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
			// 设置参数
			pstmt.setString(1, no);
			// 执行查询
			ResultSet rs = pstmt.executeQuery();
			if (!rs.next()) {
				return null;
			}
			return createStudent(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
}
